# coding=utf-8
'''
Excel表格 创建 写入 添加 sheet 注意只能创建xls文件。
'''
import os
import xlrd
import xlwt
from xlutils.copy import copy
__version__ = '1.0.0'
class mysheetwrite:
    def open(self,namepath,namesheet):
        self.namepath = namepath
        if os.path.exists(namepath) :
            oldWb = xlrd.open_workbook(namepath)
            self.newWb = copy(oldWb)  # 复制
            try:
                self.book = self.newWb.get_sheet(namesheet)  # 取sheet表
            except :
                self.book = self.newWb.add_sheet(namesheet)
            else:
                pass
        else:
            self.newWb = xlwt.Workbook(encoding='utf-8')
            self.book = self.newWb.add_sheet(namesheet)

    def changesheet(self,namesheet):
        try:
            self.book = self.newWb.get_sheet(namesheet)  # 取sheet表
        except:
            self.book = self.newWb.add_sheet(namesheet)
        else:
            pass

    def save(self):
        self.newWb.save(self.namepath)
    def saveto(self,newnamepath):
        self.newWb.save(newnamepath)

    def setdata(self,row, col:int,data:str):
        self.book.write(row,col,data)

    def setdatac(self, row, strcol:str,data:str):
        strcol = strcol.upper()
        self.setdata(row, ord(strcol[0]) - ord('A'),data)

    def setdatasc(self,row, strcol:str,data:str, style):
        strcol = strcol.upper()
        self.setdatas(row, ord(strcol[0]) - ord('A'), data, style)

    def setdatas(self,row, col:int,data:str, style):
        self.book.write(row, col, data, style)

    def getbkcstyle(self,color):
        pattern = xlwt.Pattern()  # Create the pattern
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
        pattern.pattern_fore_colour = color
        #  0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...

        style = xlwt.XFStyle()  # Create the pattern
        style.pattern = pattern
        return style


if __name__ == '__main__':
    book = mysheetwrite()
    book.open("test.xlsx","haha")
    book.setdata(0,0,"haha")
    book.saveto("test2.xls")